﻿CREATE TABLE [core].[Version]
(
	PK_Version INT NOT NULL IDENTITY,
	/* The Version number (VVVVV.VVVVV.VVVVV.VVVVV) / (major.minor.maintenance.build) */
	VersionNumber VARCHAR(25) NOT NULL,
	/* Date of the Rollout */
	RolloutDate DATE NOT NULL,
	/* Is the Version the Current version */
	IsCurrent BIT NOT NULL,
	
	/* Default Columns */
	CreatedAt DATETIME NOT NULL,
	CreatedBy NVARCHAR(256) NOT NULL,
	CreatedFromSource NVARCHAR(256) NOT NULL,
	ModifiedAt DATETIME,
	ModifiedBy NVARCHAR(256),
	ModifiedFromSource NVARCHAR(256)
	
) ON [FG_CORE]
GO

ALTER TABLE [core].[Version]
	ADD CONSTRAINT PK_Version
		PRIMARY KEY(PK_Version) ON [FG_INDEXES]
GO

ALTER TABLE [core].[Version]
	ADD CONSTRAINT UNIQUE_core_Version_VersionNumber
		UNIQUE(VersionNumber) ON [FG_INDEXES]

GO
ALTER TABLE [core].[Version]
	ADD CONSTRAINT DEF_core_Version_CreatedAt
		DEFAULT GETDATE() FOR CreatedAt
GO
CREATE FUNCTION core.fn_IsCorrectVersion
(
	@VersionNumber VARCHAR(25)
)
RETURNS BIT
AS
BEGIN
	DECLARE @ret BIT
	
	SELECT @ret = COUNT(*) FROM [core].[Version] v
		WHERE v.VersionNumber = @VersionNumber
		AND	v.IsCurrent = 1
	RETURN @ret
END
GO

CREATE PROCEDURE core.AddVersion
	@VersionNumber VARCHAR(25),
	@SetAsCurrent BIT = 1,
	@RolloutDate DATE = GETDATE
AS
BEGIN
	DECLARE	@v_VersionNumber VARCHAR(25),
			@v_SetAsCurrent BIT,
			@v_RolloutDate DATE,
			@c_Source NVARCHAR(256) = 'core.AddVersion'
			
	IF @v_RolloutDate IS NULL
	BEGIN
		SET @v_RolloutDate = GETDATE()
	END
	
	IF @v_SetAsCurrent IS NULL
	BEGIN
		SET @v_SetAsCurrent = 1
	END

	BEGIN TRANSACTION
		INSERT INTO [core].[Version](VersionNumber, RolloutDate, CreatedBy, CreatedAt, CreatedFromSource)
		SELECT @v_VersionNumber, @v_RolloutDate, SUSER_SNAME(), GETDATE(), @c_Source
		
		IF @v_SetAsCurrent = 1
		BEGIN
			
			/* Set the old Version that is current to the not current version */
			UPDATE [core].[Version]
				SET IsCurrent = 0,
					ModifiedAt = GETDATE(),
					ModifiedBy = SUSER_SNAME(),
					ModifiedFromSource = @c_Source
				WHERE IsCurrent = 1
			
			/* Set the new current version */
			UPDATE [core].[Version]
				SET IsCurrent = 1
				WHERE VersionNumber = @v_VersionNumber
			
		END
	
	COMMIT TRANSACTION
END
GO

ALTER TABLE core.Logging
	ADD LogMessage NVARCHAR(1024),
		ErrorCode INT
GO

CREATE FUNCTION core.fn_GetCurrentVersion() RETURNS VARCHAR(25)
AS
BEGIN
	DECLARE @ret VARCHAR(25)
	
	SELECT @ret = VersionNumber FROM core.Version
		WHERE IsCurrent = 1
		
	RETURN @ret
END
GO

ALTER PROCEDURE core.SaveLogging
	@LogDate DATETIME = GETDATE,
	@LogType INT,
	@VersionNumber VARCHAR(25),
	@CreatedBy NVARCHAR(256),
	@CreatedFromSource NVARCHAR(256),
	@LanguageID INT = NULL,
	@SessionID VARCHAR(50) = NULL
AS
BEGIN
	/* SSet the values in local variables */
	DECLARE
		@v_LogDate DATETIME = @LogDate,
		@v_LogType INT = @LogType,
		@v_VersionNumber VARCHAR(25) = @VersionNumber,
		@v_CreatedBy NVARCHAR(256) = @CreatedBy,
		@v_CreatedFromSource NVARCHAR(256) = @CreatedFromSource,
		@v_LanguageID INT = @LanguageID,
		@v_SessionID VARCHAR(50) = @SessionID,
		
		@v_currentVersionNumber VARCHAR(25)
	
	/* Error variables */
	DECLARE
		@v_errorCode INT,
		@v_errorText NVARCHAR(1024)
	
	/* if @LogDate is null, then set the current date timestamp */
	IF @v_LogDate IS NULL
	BEGIN
		SET @v_LogDate = GETDATE()
	END
	
	/* if LanguageID is null, then set it to default */
	IF @v_LanguageID IS NULL
	BEGIN
		SET @v_LanguageID = 1031 /* ??? from Settings-Table ??? */
	END
	
	/* if the logtype is null */
	IF @v_LogType IS NULL
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_LOGTYPE_IS_NULL', @v_LanguageID) error
	END
	
	/* if the logtype is not between 0 and 3 */
	IF @v_LogType NOT BETWEEN 0 AND 3
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_LOGTYPE_NOT_VALID', @v_LanguageID) error
	END
	
	/* If the given version not the current version */
	IF core.fn_IsCorrectVersion(@v_VersionNumber) = 0
	BEGIN
		SELECT @v_errorCode = error.ErrorCode, @v_errorText = error.ErrorText FROM core.fn_tbl_GetErrorCode('CORE_LOGGING_INCORRECT_VERSION', @v_LanguageID) error
		SELECT @v_currentVersionNumber = core.fn_GetCurrentVersion()
		
		INSERT INTO core.Logging(LogDate, LogType, VersionNumber, SessionID, ErrorCode, LogMessage, CreatedAt, CreatedBy, CreatedFromSource)
		SELECT @v_LogDate, 3, @v_currentVersionNumber, @v_SessionID, @v_errorCode, @v_errorText, GETDATE(), @v_CreatedBy, @v_CreatedFromSource
	END
	
	SELECT NULL
END
GO